<?php

function get_budget_out($money2_id, $money3_id) {
    $sql = "SELECT SUM( bill_value ) AS bill_value"
            . " FROM bill1 "
            . " WHERE bill_status =  'T' "
            . " AND confirm >=1 "
            . " AND money2_id = '$money2_id' ";
    if ($money3_id) {
        $sql = $sql . " AND money3_id = '$money3_id' ";
    }

    $row = mysql_fetch_object(mysql_query($sql));

    return $row->bill_value;
}

function get_budget_out_use($money2_id, $money3_id, $budget_year) {
    $sql = "SELECT SUM( amount ) AS net "
            . " FROM tbl_petout "
            . " WHERE approved_status =  'อนุมัติ' "
            . " AND panngan2_id = '$panngan2_id' "
            . " AND panngan3_id= '$panngan3_id' "
            . " AND budget_year = '$budget_year' "
            . " AND money_out2_id = '$money2_id' ";
    if ($money3_id) {
        $sql = $sql . " AND money_out3_id = '$money3_id' ";
    }

    $row = mysql_fetch_object(mysql_query($sql));

    return $row->net;
}

function thainumDigit($num) {
    return str_replace(array('0', '1', '2', '3', '4', '5', '6', '7', '8', '9'), array("o", "๑", "๒", "๓", "๔", "๕", "๖", "๗", "๘", "๙"), $num);
}

function get_budget($panngan2_id, $panngan3_id, $ex1_id, $ex2_id, $ex3_id, $year_budget) {
    mysql_select_db("budget");
    $sql = "SELECT SUM(budget1 + budget2 + budget3) AS budget FROM plan1, plan1_detail "
            . " WHERE plan1.year1_id = plan1_detail.year1_id "
            . " AND panngan2_id = '$panngan2_id' "
            . " AND panngan3_id= '$panngan3_id' ";

    if ($ex1_id) {
        $sql = $sql . " AND ex1_id = '$ex1_id' ";
    }
    if ($ex2_id) {
        $sql = $sql . " AND ex2_id = '$ex2_id' ";
    }
    if ($ex3_id) {
        $sql = $sql . " AND ex3_id = '$ex3_id' ";
    }
    if ($year_budget) {
        $sql = $sql . " AND plan1.year1_name = '$year_budget' ";
    }

    $row = mysql_fetch_object(mysql_query($sql));
    mysql_select_db("account");

    return $row->budget;
//    return $sql;
}

function get_budget_used($panngan2_id, $panngan3_id, $ex1_id, $ex2_id, $ex3_id, $year_budget) {

    $sql = "SELECT SUM( net ) AS net "
            . " FROM tbl_pet "
            . " WHERE approved_status =  'อนุมัติ' "
            . " AND panngan2_id = '$panngan2_id' "
            . " AND panngan3_id= '$panngan3_id' "
            . " AND ex1_id = '$ex1_id' "
//            . " AND ex2_id = '$ex2_id' "
//            . " AND ex3_id = '$ex3_id' "
            . " UNION "
            . " SELECT SUM( inpNet ) AS net "
            . " FROM tbl_petition"
            . " WHERE approved_status =  'อนุมัติ' "
            . " AND ex1_id = '$ex1_id' "
//            . " AND ex2_id = '$ex2_id' "
//            . " AND ex3_id = '$ex3_id' "
            . " UNION "
            . " SELECT SUM( salary ) AS net "
            . " FROM tbl_salary "
            . " WHERE approved_status =  'อนุมัติ' "
            . " AND panngan2_id = '$panngan2_id' "
            . " AND panngan3_id= '$panngan3_id' "
            . " AND ex1_id = '$ex1_id' "
//            . " AND ex2_id = '$ex2_id' "
//            . " AND ex3_id = '$ex3_id' "
            . " UNION "
            . " SELECT SUM( welfare ) AS net "
            . " FROM tbl_welfare "
            . " WHERE approved_status =  'อนุมัติ' "
            . " AND panngan2_id = '$panngan2_id' "
            . " AND panngan3_id= '$panngan3_id' "
            . " AND ex1_id = '$ex1_id' "
//            . " AND ex2_id = '$ex2_id' "
//            . " AND ex3_id = '$ex3_id' "
            . " ";

    mysql_select_db("account");
    $result = mysql_query($sql);
    $sum = 0;

    while ($rows = mysql_fetch_object($result)) {
        if (!empty($rows->net) && !is_null($rows->net)) {
            $sum = $sum + $rows->net;
        }
    }
    return $sum;
}

function get_name_person($card_id) {
    mysql_select_db("hr_udoncity");

    $sql = "select fname, lname from person where id = '$card_id'";

    $row = mysql_fetch_object(mysql_query($sql));

    mysql_select_db("account");

    return $row->fname . "  " . $row->lname;
}

function get_std_2($year_month) {
    
    if ($year_month) {
        $where = "AND DATE_FORMAT( date_create,  '%Y-%m' ) = '$year_month' ";
    }
    mysql_select_db("hr_udoncity");
    
    $sql = " SELECT expenses2_name, e_id, SUM( salary ) AS net "
        . " FROM tbl_salary, expenses_lvl2 "
        . " WHERE expenses_lvl2.expenses2_id = ex2_id "
        . " $where "
        . " GROUP BY expenses2_name, e_id "
        . " UNION "
        . " SELECT expenses2_name, e_id, SUM(net) AS net "
        . " FROM tbl_pet,  expenses_lvl2 "
        . " WHERE expenses_lvl2.expenses2_id = ex2_id "
        . " $where "
        . " GROUP BY expenses2_name, e_id ";
    
    return mysql_query($sql);
    
}

function get_running_finance($budget_year) {
    //@session_start();

    $sql = "SELECT  MAX(running_id) as running_id "
            . " FROM temp_genkey_fin "
            . " WHERE budget_year = '$budget_year'";
    // = new myDBS;
    mysql_select_db("account");
    $result = mysql_query($sql);
    $rows = mysql_fetch_object($result);

    if (is_null($rows->running_id)) {
        $running = 0;
        $sql = "INSERT INTO temp_genkey_fin (running_id, budget_year) "
                . "VALUES('1', '$budget_year')";
        mysql_query($sql);
    } else {
        $running = $rows->running_id;
        $sql = "UPDATE temp_genkey_fin "
                . " SET running_id = running_id + 1 "
                . " WHERE budget_year = '$budget_year'";
        mysql_query($sql);
    }
    $running = $running + 1;
    $running = $running . "-" . $budget_year;

    return "ค.".$running;
}

function get_panngan2_name($panngan2_id) {
    $sql = "select panngan2_name from panngan2 where panngan2_id = '$panngan2_id'";

    $row = mysql_fetch_object(mysql_query($sql));

    return $row->panngan2_name;
}

function get_panngan3_name($panngan3_id) {
    $sql = "select panngan3_name from panngan3 where panngan3_id = '$panngan3_id'";

    $row = mysql_fetch_object(mysql_query($sql));

    return $row->panngan3_name;
}

function gen_account_id($panngan2_id, $panngan3_id, $e_id) {
    $aa = split("0", $panngan2_id);
    $bb = "";
    for ($i = 0; $i < 4; $i++) {
        if ($aa[$i] == "" && ($i > 0)) {
            $aa[$i] = "0";
        }
        $bb.=$aa[$i];
    }
    $aa = split("0", $panngan3_id);
    $bb1 = "";
    for ($i = 0; $i < 4; $i++) {
        if ($aa[$i] == "" && ($i > 0)) {
            $aa[$i] = "0";
        }
        $bb1.=$aa[$i];
    }
    return $bb . "-" . $bb1 . "-" . $e_id;
}

function get_config() {
    $sql = "SELECT * FROM tbl_config "
            . " WHERE id = '1' ";

    return mysql_fetch_assoc(mysql_query($sql));
}

class myDBS {

    function selectDB($dbname) {
        mysql_select_db($dbname) or die("Don't select table");
    }

    function insData($sql) {
        mysql_query($sql);
        return mysql_insert_id();
    }

    function runSQL($sql) {
        $result = mysql_query($sql);
        return $result;
    }

    function showData($tablename) {
        $sql = "SELECT * FROM $tablename";
        $result = mysql_query($sql);

        return $result;
    }

    function showData1($tablename, $where) {
        $sql = "SELECT * FROM $tablename WHERE $where";
        $result = mysql_query($sql);

        return $result;
    }

    function fetch_object($result) {
        return mysql_fetch_object($result);
    }

    function delData_byID($tablename, $id) {
        $sql = "DELETE FROM $tablename WHERE id = '$id'";
        $result = mysql_query($sql);

        return $result;
    }

    function num_rows($result) {
        return mysql_num_rows($result);
    }

    function result($result) {
        return mysql_result($result, 0);
    }

    function get_name($field_id, $field_name, $tablename, $value) {
        $sql = "select $field_name from $tablename where $field_id = '$value'";
        $result = mysql_query($sql);
        $rows = mysql_fetch_object($result);

        return $rows->{$field_name};
        //return $sql;
    }

    function get_running($budget_year) {
        @session_start();

        $sql = "SELECT  MAX(running_id) as running_id FROM temp_running WHERE lvl1_id = '" . $_SESSION["lvl1_id"] . "' and budget_year = '$budget_year'";
        // = new myDBS;
        $this->selectDB("account");
        $result = $this->runSQL($sql);
        $rows = $this->fetch_object($result);

        if (is_null($rows->running_id)) {
            $running = 0;
            $sql = "INSERT INTO temp_running (running_id, lvl1_id, budget_year) VALUES('1', '" . $_SESSION["lvl1_id"] . "', '$budget_year')";
            $this->insData($sql);
        } else {
            $running = $rows->running_id;
            $sql = "UPDATE temp_running SET running_id = running_id + 1 WHERE lvl1_id = '" . $_SESSION["lvl1_id"] . "' and budget_year = '$budget_year'";
            $this->runSQL($sql);
        }
        $running = $running + 1;
        $running = $running . "-" . $budget_year;

        return $running;
    }

}

class Currency {

    public function bahtEng($thb) {
        list($thb, $ths) = explode('.', $thb);
        $ths = substr($ths . '00', 0, 2);
        $thb = Currency::engFormat(intval($thb)) . ' Baht';
        if (intval($ths) > 0) {
            $thb .= ' and ' . Currency::engFormat(intval($ths)) . ' Satang';
        }
        return $thb;
    }

    // ตัวเลขเป็นตัวหนังสือ (ไทย)
    public function bahtThai($thb) {
        list($thb, $ths) = explode('.', $thb);
        $ths = substr($ths . '00', 0, 2);
        $thaiNum = array('', 'หนึ่ง', 'สอง', 'สาม', 'สี่', 'ห้า', 'หก', 'เจ็ด', 'แปด', 'เก้า');
        $unitBaht = array('บาท', 'สิบ', 'ร้อย', 'พัน', 'หมื่น', 'แสน', 'ล้าน', 'สิบ', 'ร้อย', 'พัน', 'หมื่น', 'แสน', 'ล้าน');
        $unitSatang = array('สตางค์', 'สิบ');
        $THB = '';
        $j = 0;
        for ($i = strlen($thb) - 1; $i >= 0; $i--, $j++) {
            $num = $thb[$i];
            $tnum = $thaiNum[$num];
            $unit = $unitBaht[$j];
            switch (true) {
                case $j == 0 && $num == 1 && strlen($thb) > 1:
                    $tnum = 'เอ็ด';
                    break;
                case $j == 1 && $num == 1:
                    $tnum = '';
                    break;
                case $j == 1 && $num == 2:
                    $tnum = 'ยี่';
                    break;
                case $j == 6 && $num == 1 && strlen($thb) > 7:
                    $tnum = 'เอ็ด';
                    break;
                case $j == 7 && $num == 1:
                    $tnum = '';
                    break;
                case $j == 7 && $num == 2:
                    $tnum = 'ยี่';
                    break;
                case $j != 0 && $j != 6 && $num == 0:
                    $unit = '';
                    break;
            }
            $S = $tnum . $unit;
            $THB = $S . $THB;
        }
        if ($ths == '00') {
            $THS = 'ถ้วน';
        } else {
            $j = 0;
            $THS = '';
            for ($i = strlen($ths) - 1; $i >= 0; $i--, $j++) {
                $num = $ths[$i];
                $tnum = $thaiNum[$num];
                $unit = $unitSatang[$j];
                switch (true) {
                    case $j == 0 && $num == 1 && strlen($ths) > 1:
                        $tnum = 'หนึ่ง';
                        break;
                    case $j == 1 && $num == 1:
                        $tnum = '';
                        break;
                    case $j == 1 && $num == 2:
                        $tnum = 'ยี่';
                        break;
                    case $j != 0 && $j != 6 && $num == 0:
                        $unit = '';
                        break;
                }
                $S = $tnum . $unit;
                $THS = $S . $THS;
            }
        }
        return $THB . $THS;
    }

    // ตัวเลขเป็นตัวหนังสือ (eng)
    private function engFormat($number) {
        list($thb, $ths) = explode('.', $thb);
        $ths = substr($ths . '00', 0, 2);
        $max_size = pow(10, 18);
        if (!$number)
            return "zero";
        if (is_int($number) && $number < abs($max_size)) {
            switch ($number) {
                case $number < 0:
                    $prefix = "negative";
                    $suffix = Currency::engFormat(-1 * $number);
                    $string = $prefix . " " . $suffix;
                    break;
                case 1:
                    $string = "one";
                    break;
                case 2:
                    $string = "two";
                    break;
                case 3:
                    $string = "three";
                    break;
                case 4:
                    $string = "four";
                    break;
                case 5:
                    $string = "five";
                    break;
                case 6:
                    $string = "six";
                    break;
                case 7:
                    $string = "seven";
                    break;
                case 8:
                    $string = "eight";
                    break;
                case 9:
                    $string = "nine";
                    break;
                case 10:
                    $string = "ten";
                    break;
                case 11:
                    $string = "eleven";
                    break;
                case 12:
                    $string = "twelve";
                    break;
                case 13:
                    $string = "thirteen";
                    break;
                case 15:
                    $string = "fifteen";
                    break;
                case $number < 20:
                    $string = Currency::engFormat($number % 10);
                    if ($number == 18) {
                        $suffix = "een";
                    } else {
                        $suffix = "teen";
                    }
                    $string .= $suffix;
                    break;
                case 20:
                    $string = "twenty";
                    break;
                case 30:
                    $string = "thirty";
                    break;
                case 40:
                    $string = "forty";
                    break;
                case 50:
                    $string = "fifty";
                    break;
                case 60:
                    $string = "sixty";
                    break;
                case 70:
                    $string = "seventy";
                    break;
                case 80:
                    $string = "eighty";
                    break;
                case 90:
                    $string = "ninety";
                    break;
                case $number < 100:
                    $prefix = Currency::engFormat($number - $number % 10);
                    $suffix = Currency::engFormat($number % 10);
                    $string = $prefix . "-" . $suffix;
                    break;
                case $number < pow(10, 3):
                    $prefix = Currency::engFormat(intval(floor($number / pow(10, 2)))) . " hundred";
                    if ($number % pow(10, 2))
                        $suffix = " and " . Currency::engFormat($number % pow(10, 2));
                    $string = $prefix . $suffix;
                    break;
                case $number < pow(10, 6):
                    $prefix = Currency::engFormat(intval(floor($number / pow(10, 3)))) . " thousand";
                    if ($number % pow(10, 3))
                        $suffix = Currency::engFormat($number % pow(10, 3));
                    $string = $prefix . " " . $suffix;
                    break;
                case $number < pow(10, 9):
                    $prefix = Currency::engFormat(intval(floor($number / pow(10, 6)))) . " million";
                    if ($number % pow(10, 6))
                        $suffix = Currency::engFormat($number % pow(10, 6));
                    $string = $prefix . " " . $suffix;
                    break;
                case $number < pow(10, 12):
                    $prefix = Currency::engFormat(intval(floor($number / pow(10, 9)))) . " billion";
                    if ($number % pow(10, 9))
                        $suffix = Currency::engFormat($number % pow(10, 9));
                    $string = $prefix . " " . $suffix;
                    break;
                case $number < pow(10, 15):
                    $prefix = Currency::engFormat(intval(floor($number / pow(10, 12)))) . " trillion";
                    if ($number % pow(10, 12))
                        $suffix = Currency::engFormat($number % pow(10, 12));
                    $string = $prefix . " " . $suffix;
                    break;
                case $number < pow(10, 18):
                    $prefix = Currency::engFormat(intval(floor($number / pow(10, 15)))) . " quadrillion";
                    if ($number % pow(10, 15))
                        $suffix = Currency::engFormat($number % pow(10, 15));
                    $string = $prefix . " " . $suffix;
                    break;
            }
        }
        return $string;
    }

}
?>